package com.apes.fn.ssm.repository;

import com.apes.fn.ssm.model.CalculatedState;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface CalculatedStateRepository extends CustomRepository<CalculatedState, String> {
    @Query(value = "" +
            "SELECT LOCATION_ID, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, " +
            "       (SELECT NAME FROM MAS_FITTING_MESSAGE WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "       DECODE(CALCU_COVERAGE_MARK, 0, '未开始', 1, '进行中', 2, '已完成', '') CALCU_COVERAGE_MARK, " +
            "       DECODE(CALCU_QUALIFY_MARK, 0, '未开始', 1, '进行中', 2, '已完成', '') CALCU_QUALIFY_MARK, " +
            "       DECODE(CALCU_SALES_MARK, 0, '未开始', 1, '进行中', 2, '已完成', '') CALCU_SALES_MARK, " +
            "       DECODE(CALCU_STOCK_PRO_MARK, 0, '未开始', 1, '进行中', 2, '已完成', '') CALCU_STOCK_PRO_MARK, " +
            "       DECODE(VIP_MODEL, 0, '未开始', 1, '进行中', 2, '已完成', '') VIP_MODEL," +
            "       DECODE(CALCU_STOCK_QTY_MARK, 0, '未开始', 1, '进行中', 2, '已完成', '') CALCU_STOCK_QTY_MARK, " +
            "       CASE WHEN DATA_CENTER_MODEL_MARK = 0 AND DATA_CENTER_MODEL_MARK2 = 0 THEN '未开始' " +
            "         WHEN DATA_CENTER_MODEL_MARK = 1 OR DATA_CENTER_MODEL_MARK2 = 1 OR (DATA_CENTER_MODEL_MARK = 2 AND DATA_CENTER_MODEL_MARK2 = 0) " +
            "           OR (DATA_CENTER_MODEL_MARK = 0 AND DATA_CENTER_MODEL_MARK2 = 2) THEN '进行中' " +
            "         WHEN DATA_CENTER_MODEL_MARK = 2 AND DATA_CENTER_MODEL_MARK2 = 2 THEN '已完成' " +
            "         ELSE '失败' END DATA_CENTER_MODEL_MARK, " +
            "       DECODE(CALCU_COVERAGE_MARK + CALCU_QUALIFY_MARK + CALCU_SALES_MARK + CALCU_STOCK_PRO_MARK + VIP_MODEL + CALCU_STOCK_QTY_MARK, 12, '已完成', '0', '未进行', '进行中') COMPLETE " +
            "  FROM MAS_CALCULATED_STATE A ", nativeQuery = true)
    List<Map> findAlls();

    @Query(value = "" +
            "SELECT DISTINCT LOCATION_ID " +
            "  FROM MAS_CALCULATED_STATE " +
            " WHERE DATA_CENTER_MODEL_MARK2 = 2 " +
            "   AND DATA_CENTER_MODEL_MARK = 2 " +
            "   AND (CALCU_COVERAGE_MARK = 0 OR CALCU_QUALIFY_MARK = 0) " +
            "   AND LOCATION_ID NOT IN (SELECT LOCATION_ID FROM MAS_CALCULATED_STATE WHERE DATA_CENTER_MODEL_MARK2 != 2 OR DATA_CENTER_MODEL_MARK != 2) ", nativeQuery = true)
    List<Map> findUndone();

    @Query(value = "" +
            "SELECT LOCATION_ID " +
            "  FROM MAS_CALCULATED_STATE T " +
            " WHERE (CALCU_COVERAGE_MARK != 2 OR CALCU_QUALIFY_MARK != 2 OR " +
            "       CALCU_SALES_MARK != 2 OR CALCU_STOCK_PRO_MARK != 2 OR " +
            "       CALCU_STOCK_QTY_MARK != 2 OR DATA_CENTER_MODEL_MARK != 2 OR " +
            "       DATA_CENTER_MODEL_MARK2 != 2 OR VIP_MODEL != 2) " +
            "   AND ROWNUM = 1 ", nativeQuery = true)
    List<Map> judgeDone();
}
